Skip Headers

Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 2 The Sample Schema

This guide uses the Global schema for its examples. This chapter describes this schema and explains how it will be mapped to dimensional objects. It consists of the following topics:

- Case Study Scenario

- Identifying Required Business Facts

- Designing a Logical Data Model for Global Computing

- The Global Schema

## Case Study Scenario

The fictional Global Computing Company was established in 1990. Global Computing distributes computer hardware and software components to customers on a worldwide basis. The Sales and Marketing department has not been meeting its budgeted numbers. As a result, this department has been challenged to develop a successful sales and marketing strategy.

Global Computing operates in an extremely competitive market. Competitors are numerous, customers are especially price-sensitive, and profit margins tend to be narrow. In order to grow profitably, Global Computing must increase sales of its most profitable products.

Various factors in Global Computing's current business point to a decline in sales and profits:

- Traditionally, Global Computing experiences low third-quarter sales (July through September). However, recent sales in other quarters have also been lower than expected. The company has experienced bursts of growth but, for no apparent reason, has had lower first-quarter sales during the last two years as compared with prior years.

- Global has been successful with its newest sales channel, the Internet. Although sales within this channel are growing, overall profits are declining.

- Perhaps the most significant factor is that margins on personal computers - previously the source of most of Global Computing's profits - are declining rapidly.

Global Computing needs to understand how each of these factors is affecting its business.

Current reporting is done by the IT department, which produces certain standard reports on a monthly basis. Any ad hoc reports are handled on an as-needed basis and are subject to the time constraints of the limited IT staff. Complaints have been widespread within the Sales and Marketing department, with regard to the delay in response to report requests. Complaints have also been numerous in the IT department, with regard to analysts who change their minds frequently or ask for further information.

The Sales and Marketing department has been struggling with a lack of timely information about what it is selling, who is buying, and how they are buying. In a meeting with the CIO, the VP of Sales and Marketing states, "By the time I get the information, it's no longer useful. I'm only able to get information at the end of each month, and it doesn't have the details I need to do my job."

### Reporting Requirements

When asked to be more specific about what she needs, the Vice President of Sales and Marketing identifies the following requirements:

- Trended sales data for specific customers, regions, and segments.

- The ability to provide information and some analysis capabilities to the field sales force. A Web interface would be preferred, since the sales force is distributed throughout the world.

- Detail regarding mail-order, phone, and e-mail sales on a monthly and quarterly basis, as well as a comparison to past time periods. Information must identify when, how, and what is being sold by each channel.

- Margin information on products in order to understand the dollar contribution for each sale.

- Knowledge of percent change versus the prior and year-ago period for sales, units, and margin.

- The ability to perform analysis of the data by ad hoc groupings.

The CIO has discussed these requirements with his team and has come to the conclusion that a standard reporting solution against the production order entry system would not be flexible enough to provide the required analysis capabilities. The reporting requirements for business analysis are so diverse that the projected cost of development, along with the expected turnaround time for requests, would make this solution unacceptable.

The CIO's team recommends using an analytic workspace to support analysis. The team suggests that the Sales and Marketing department's IT group work with Corporate IT to build an analytic workspace that meets their needs for information analysis.

### Business Goals

The development team identifies the following high-level business goals that the project must meet:

- Global Computing's strategic goal is to increase company profits by increasing sales of higher margin products and by increasing sales volume overall.

- The Sales and Marketing department objectives are to:

  - Analyze industry trends and target specific market segments

  - Analyze sales channels and increase profits

  - Identify product trends and create a strategy for developing the appropriate channels

### Information Requirements

Once you have established business goals, you can determine the type of information that will help achieve these goals. To understand how end users will examine the data in the analytic workspace, it is important to conduct extensive interviews. From interviews with key end users, you can determine how they look at the business, and what types of business analysis questions they want to answer

#### Business Analysis Questions

Interviews with the VP of Sales and Marketing, salespeople, and market analysts at Global Computing reveal the following business analysis questions:

- What products are profitable?

- Who are our customers, and what and how are they buying?

- What accounts are most profitable? What is the performance of each distribution channel?

- Is there still a seasonal variance to the business?

We can examine each of these business analysis questions in detail.

#### What products are profitable?

This business analysis question consists of the following questions:

- What is the percent of total sales for any item, product family, or product class in any month, quarter or year, and in any distribution channel? How does this percent of sales differ from a year ago?

- What is the unit price, unit cost, and margin for each unit for any item in any particular month? What are the price, cost, and margin trends for any item in any month?

- What items were most profitable in any month, quarter, or year, in any distribution channel, and in any geographic area or market segment? How did profitability change from the prior period? What was the percent change in profitability from the prior period?

- What items experienced the greatest change in profitability from the prior period?

- What items contributed the most to total profitability in any month, quarter, or year, in any distribution channel, and in any geographic area or market segment?

- What items have the highest per unit margin for any particular month?

- In summary, what are the trends?

#### Who are our customers, and what and how are they buying?

This business analysis question consists of the following questions:

- What were sales for any item, product family, or product class in any month, quarter, or year?

- What were sales for any item, product family, or product class in any distribution channel, geographic area, or market segment?

- How did sales change from the prior period? What was the percent change in sales from the prior period?

- How did sales change from a year ago? What was the percent change in sales from a year ago?

- In summary, what are the trends?

#### What accounts are most profitable?

This business analysis question consists of the following questions:

- What accounts are most profitable in any month, quarter, or year, in any distribution channel, by any item, product family, or product class?

- What were sales and extended margin (gross profit) by account for any month, quarter, or year, for any distribution channel, and for any product?

- How does account profitability compare to the prior time period?

- Which accounts experienced the greatest increase in sales as compared to the prior period?

- What is the percent change in sales from the prior period? Did the percent change in profitability increase at the same rate as the percent change in sales?

- In summary, what are the trends?

#### What is the performance of each distribution channel?

This business analysis question consists of the following questions:

- What is the percent of sales to total sales for each distribution channel for any item, product family, or product class, or for any geographic area or market segment?

- What is the profitability of each distribution channel: direct sales, catalog sales, and the Internet?

- Is the newest distribution channel, the Internet, "cannibalizing" catalog sales? Are customers simply switching ordering methods, or is the Internet distribution channel reaching additional customers?

- In summary, what are the trends?

#### Is there still a seasonal variance to the business?

This business analysis question consists of the following questions:

- Are there identifiable seasonal sales patterns for particular items or product families?

- How do seasonal sales patterns vary by geographic location?

- How do seasonal sales patterns vary by market segment?

- Are there differences in seasonal sales patterns as compared to last year?

#### Summary of Information Requirements

By examining the types of analyses that users wish to perform, we can identify the following key requirements for analysis:

- Global Computing has a strong need for profitability analysis. The company must understand profitability by product, account, market segment, and distribution channel. It also needs to understand profitability trends.

- Global Computing needs to understand how sales vary by time of year. The company must understand these seasonal trends by product, geographic area, market segment, and distribution channel.

- Global Computing has a need for ad hoc sales analysis. Analysis must identify what products are sold to whom, when these products are sold, and how customers buy these products.

- The ability to perform trend analysis is important to Global Computing.

## Identifying Required Business Facts

The key analysis requirements reveal the business facts that are required to support analysis requirements at Global Computing.

These facts are ordered by time, product, customer shipment or market segment, and distribution channel:

Sales
Units
Change in sales from prior period
Percent change in sales from prior period
Change in sales from prior year
Percent change in sales from prior year
Product share
Channel share
Market share
Extended cost
Extended margin
Extended margin change from prior period
Extended margin percent change from prior period
Units sold, change from prior period
Units sold, percent change from prior period
Units sold, change from prior year
Units sold, percent change from prior year

These facts are ordered by item and month:

Unit price
Unit cost
Margin per unit

## Designing a Logical Data Model for Global Computing

"Business Goals" identifies the business facts that will support analysis requirements at Global Computing. Next, we will identify the dimensions, levels, and attributes in a logical data model. We will also identify the relationships within each dimension. The resulting data model will be used to design the Global schema, the logical dimensional model, and the analytic workspace.

### Identifying Dimensions

Four dimensions will be used to organize the facts in the database.

- Product shows how data varies by product.

- Customer shows how data varies by customer or geographic area.

- Channel shows how data varies according to each distribution channel.

- Time shows how data varies over time.

### Identifying Levels

Now that we have identified dimensions, we can identify the levels of summarization within each dimension. Analysis requirements at Global Computing reveal that:

- There are three distribution channels: Sales, Catalog, and Internet. These three values are the lowest level of detail in the data warehouse and will be grouped in the Channel level. From the order of highest level of summarization to the lowest level of detail, levels will be Total Channel and Channel.

- Global performs customer and geographic analysis along the line of shipments to customers and by market segmentation. Shipments and Market Segment will be two hierarchies in the Customer dimension. In each case, the lowest level of detail in the data model is the Ship To location.

  - When analyzing along the line of customer shipments, the levels of summarization will be (highest to lowest): Total Customer, Region, Warehouse, and Ship To.

  - When analyzing by market segmentation, the levels of summarization will be (highest to lowest): Total Market, Market Segment, Account, and Ship To.

- In the examples in this guide, Product is mapped to a parent-child table and is defined as a value-based hierarchy rather than a level-based hierarchy. Thus, no levels are defined for Product.

- The Time dimension will have three levels (highest to lowest): Year, Quarter, and Month.

Within the Channel, Customer, and Product dimensions, we added a Total or All level as the highest level of summarization. Adding this highest level provides additional flexibility as application users analyze data.

### Identifying Hierarchies

We will identify the hierarchies that organize the levels within each dimension. To identify hierarchies, we will group the levels in the correct order of summarization and in a way that supports the identified types of analysis.

For the Channel, Product, and Time dimensions, Global Computing requires only one hierarchy for each dimension. For the Customer dimension, however, Global Computing requires two hierarchies. Analysis within the Customer dimension tends to be either by geographic area or market segment. Therefore, we will organize levels into two hierarchies, Shipments and Market Segment.

### Identifying Stored Measures

"Identifying Required Business Facts" lists 21 business facts that are required to support the analysis requirements of Global Computing. Of this number, only four facts need to be acquired from the transactional database:

- Units

- Sales

- Unit Price

- Unit Cost

All of the other facts can be derived from these basic facts. The derived facts can be calculated in the analytic workspace on demand. If experience shows that some of these derived facts are being used heavily and the calculations are putting a noticeable load on the system, then some of these facts can be calculated and stored in the analytic workspace as a data maintenance procedure.

## The Global Schema

You can download the Global schema from

    http://www.oracle.com/technology/products/bi/olap/olap.html

and use it to try the examples shown throughout this guide. Instructions for installing the schema are provided in the readme file.

The Global schema contains alternative data sources for the logical Global model, so that you can explore these variations:

- Star schema

- Snowflake schema

- Parent-child table

The examples in this guide use a parent-child table for the Product dimension, a star schema for the Customer and Channel dimensions, and a snowflake schema for the Time dimension. See Chapter 3 for schema diagrams.